Shareware Grab Bag/Shareware Grab Bag.iso/096/rbnotes2.arc/EDTORIAL.10

Text File | 1986-01-31 | 17.5 KB | 529 lines

*********************************************************
*   TECHNICAL EDITORIAL NUMBER 10 -- JANUARY 31, 1986   *
*********************************************************
Solutions and explanations for some commonly encountered problems.


===========================================================================
RESETTING REPORT VARS
===========================================================================
Product: R:base 5000 ver. 1.01

DESCRIPTION:  When I set up breakpoints in the R:base 5000 Report Writer, I
go into (M)ark mode and choose the (B)reak option and R:base asks me, "Do
you want to manually reset break variables[NO]?"

What does "manually reset" mean?


EXPLANATION:  The majority of customers are breaking on only one column and
do not need to even worry about this.  If you fall into this catagory,
press the [ENTER] key and ignore the question.  If breaking on more than
one column, read on.

If you press [ENTER] in response to this question, the default (NO) will be
used.

In order to understand why you probably want to answer NO, you need to
understand the concepts of breaks, accumulators, and reset variables.

NO -- means you want variables that were reset in inner breaks to be
automatically reset in outer breaks.  In 99% of all cases you will want
this automatic resetting.

YES -- means you want to manually add inner break reset variables to the
reset list of the outer breaks.  This is usually not necessary.

If you are breaking on one column and then breaking on another column
"inside" the first, you have two levels of breaks.  Level one is the outer
break and level two is the inner break.  For example, you may wish to break
on COMPANY (the outer or level one break) and then break on DEPT within
COMPANY (the inner or level two break).  COMPANY and DEPT are the break
columns; they are NOT the reset variables.

Reset variables are the accumulators.  They are the variables that you want
to continue adding things up until a break is encountered.  At break time
you want the reset variables to be reset to zero.  Reset variables are
defined in the (D)efine mode of the Report Writer just like any other
report variable.  You might want, for example, to add up all expenses by
COMPANY and by DEPT within COMPANY.  You will need to set up reset
variables for each break.  For example,

1.  In (D)efine mode set up two report variables (CO-EXP and DEPT-EXP) to
    accumulate the EXPENSE column for each break column:

    CO-EXP = CO-EXP + EXPENSE
    DEPT-EXP = DEPT-EXP + EXPENSEwh

2.  Now, when in (M)ark mode:

    If reset variables are automatically reset (that is you answered NO to
    the question), you need to add CO-EXP to the reset list for the COMPANY
    break and you need to add DEPT-EXP to the reset list for the DEPT
    break.  DEPT-EXP will automatically be reset when either COMPANY or
    DEPT breaks.

    If reset variables are manually reset (that is you answered YES to the
    question), you need to add both CO-EXP and DEPT-EXP to the reset list
    for the COMPANY break and DEPT-EXP to the reset list for the DEPT
    break.  If you do not manually enter DEPT-EXP to the reset list for the
    COMPANY break, under this option, it will not be reset when a new
    COMPANY is encountered.


SOLUTION:  It is best to allow the computer to automatically reset inner
breaks' reset variables when outer breaks occur by answering NO to the
question, "Do you want to manually reset break variables".

AUTOMATIC RESETTING (ANSWER NO):
-------------------------------
BREAK ONE - RESET LIST
      var1

  BREAK TWO - RESET LIST
        var2

    BREAK THREE - RESET LIST
          var3

      BREAK FOUR - RESET LIST
            var4

MANUAL RESETTING (ANSWER YES):
-----------------------------
BREAK ONE - RESET LIST
      var1
      var2
      var3
      var4

  BREAK TWO - RESET LIST
        var2
        var3
        var4

    BREAK THREE - RESET LIST
          var3
          var4

      BREAK FOUR - RESET LIST
            var4





===========================================================================
COMMAS IN INTEGERS
===========================================================================
Product: R:base 5000
Versions 1.0, 1.01

DESCRIPTION:  I want my integer column NUMBER to print out on my report
with commas in every third (from the right) position. How do I do it?


SOLUTION:  Using the (d)efine option in reports, set up an expression like
the following:

REPVAR = NUMBER X $1.00

When you multiply by $1.00, REPVAR becomes a dollar variable.  Now,
(l)ocate REPVAR on the report instead of NUMBER and set DOLLAR to blank
before printing the report.  This method will print an extra .00 on the end
of every integer value, but it will put the commas in.

If it is absolutely necessary to get rid of the .00s and your report only
has to go to the screen, you can blank out the .00 by locating a BLANK
report variable that has three ALT 255s in it on the same line in the last
three positions.  For instructions on how to do this, see page 7 of the
September 1985 R:base EXCHANGE.

However, keep in mind that although ALT 255s are blanks to you, they are
gremlins to your printer.  If you need your report printed, it is best to
print it to a file with the .00s and then do a global search and replace
(with your word processor) to change the .00 to three blanks before
printing the report on the printer.




===========================================================================
PRINTER GREMLINS
===========================================================================
Product: R:base 5000
version 1.0, 1.01

DESCRIPTION:  I have gremlins in my printer!!  I keep encountering the
following problems:

o  My report is crooked, everything jumps to the left on some rows but not
   on others.  Printing to the screen is fine.
o  Sometimes the wrong number is printed on the paper.  But if I print the
   report to the screen or to a file, the number is correct.
o  I get a spurious form feed in the middle of the report with some
   printers.

Why is this happening?


EXPLANATION:  ALT 255s in your data, or in report variables, or in global
variables, or in the body of your report are the most likely culprits.
Most printers cannot handle an ALT 255.  An ALT 255 looks like a blank to
you and the screen has no problem with it so everything looks fine on the
screen.

How did ALT 255s get in there?

o  You may have entered it by holding down the ALT key and pressing 255 on
   the number pad.

o  The computer may have put it into a column in your table that was
   supposed to be NULL if you loaded variables using the LOAD command (a
   common procedure with variable forms) and forgot to first issue the
   following command:

   SET NULL -0-.

   If NULL was set to a blank and some of your TEXT variables were NULL you
   may have an ALT 255 in your data.

o  If the column being loaded had been any of the other datatypes (DATE,
   TIME, INTEGER, DOLLAR, or REAL), and your operator pressed [ENTER]
   (because there was no entry), and NULL was not set to -0- before the
   LOAD block, then the entire row was probably not even added to the
   table.  You would not even know it if BELL and ERROR MESSAGES had been
   set off.  It is crucial to have NULL set to -0- before a LOAD command is
   executed.  Otherwise, later you may think that the data you entered
   disappeared, when in fact it was never loaded in the first place.


SOLUTION:  First, fix the LOAD block in your R:base 5000 program code to
set NULL to -0- prior to executing the LOAD block.  Set it back to a blank
after the LOAD block and everything will look the same to the operator.
The following code will accomplish this task:

SET NULL -0-
LOAD tblname
.var1  .var2  .var3
END
SET NULL " "

Next, get the gremlins out of your data with the following commands:

SET NULL -0-
CHANGE colname TO -0- WHERE COLNAME EQ " "
*(ALT 255 between the quotes on above line.
  To enter it:  hold ALT key down, enter 255 on
  number pad, release ALT key.)

Next, check and make sure that all the rows you have entered are actually
in the database.

Finally, get the gremlins out of your reports by redefining any variables
that have been set to an ALT 255.  Set them to the type of blanks made by
the spacebar on your keyboard and you will not have any more problems.




===========================================================================
TURNING TYPE-AHEAD ON
===========================================================================
Product: R:base 5000
versions 1.0, 1.01

DESCRIPTION:  I want the user to be able to type ahead while the command
file is processing, how do I do it?

SOLUTION:  Put the following command line into your command file:

SET ESCAPE OFF

This command turns off the ability to escape processing while in the middle
of command file, WHILE loops, and database access.  With escape set off you
will not get the message "Press [Esc] to abort, any other key to continue".
Therefore, you can type ahead (until you have filled the buffer) while the
command file continues to process.




**********************
*    APPLICATIONS    *
**********************



*******************
COUNTING THE BREAKS
*******************
Product: R:base 5000
versions 1.0, 1.01

any people want an easy way to count the number of breaks in a particular
report without having to do a TALLY.  In other words you want to break on
PROJECT# and you want the report to determine how many total projects there
were so you can put some averages and other statistics into your report.

For example, consider a simple job costing application for a labor
intensive business like consulting.  This application has two tables:

JOBCOST table (transactions)
--------------------------
WORKDAY     DATE
PROJ#       INTEGER
EMPLOYEE#   INTEGER
HOURS       REAL

PROJECTS table (master)
-----------------------
PROJ#       INTEGER
PROJNAME    TEXT    40
COMPANY     TEXT    30
ADDR        TEXT    30
CITY        TEXT    20
STATE       TEXT    2
ZIP         TEXT    5

When a project contract is signed, a new entry is made to the PROJECTS
table.  Every workday each of the employees charges hours to the various
projects that they worked on.  Now you want to print a report for the
period 12/1/85 through 12/31/85 that includes the following information:

o  Number of projects worked on during the month
o  Average number of hours per project (total hours charged / total number
   of projects)

To do these statistics we need to know how many projects are included in
the report.  We cannot do a simple COMPUTE varname AS COUNT... because each
project number is duplicated many times in the JOBCOST table and we want to
count only the number of unique project numbers for the reporting period.

We could do it by using the TALLY command with the appropriate WHERE
clause, sending the output to a file, loading the file into a temporary
table, and then counting the rows of the table.  But, thank goodness, all
that is not necessary!

You can count the number of breaks inside the report writer with a simple
trick shot.

First, you need three variables: COUNTER, BREAKER, and TOT.

Increment COUNTER by one for each row and add COUNTER to the reset list for
the break column (PROJ# in this example).

Set BREAKER to 1 divided by COUNTER (BREAKER = 1 / COUNTER) and make sure
the datatype for BREAKER is INTEGER so any fraction will evaluate as zero.

Since COUNTER will only be one at break time (it will be a larger number,
such as 2, 3, etc. at other times), the variable BREAKER will be 1 only at
break time.  At all other times, BREAKER will be zero because it will be a
fraction forced into an integer result.

Therefore, the variable TOT can be used to accumulate the BREAKER variable
with the final result being the total number of breaks (that is, the total
number of unique values for the break column) in this report.

The variable expression definitions for the PROJ# example will look like
this.

1:INTEGER : COUNTER  = COUNTER + 1
2:INTEGER : BREAKER  = 1 / COUNTER
3:INTEGER : TOT      = TOT + BREAKER

Now the variable TOT is the total number of projects.  To get the overall
average number of hours per project, add the following variable definition:

4:REAL    : TOTHRS   = TOTHRS + HOURS

where HOURS is the column in the table that contains the actual hours
charged to a project.

Finally, compute the GRANDAVG with the following variable definition and
locate GRANDAVG in the report footer so it will print only once.

5:REAL    : GRANDAVG = TOTHRS / TOT

Now, the following R:base command can print the statistics you want.

PRINT rptname WHERE WORKDAY GE +
12/1/85 AND WORKDAY LE 12/31/85.

There are hundreds of applications where you could effectively use this
trick shot.  For another example, the following report counts the number of
unique accounts reported in order to report an average dollar amount per
account.

Account # ->      101
Transaction Date   Amount
----------------   ------
01/01/85          $100.00
01/10/85            $5.00
01/15/85          $127.00
01/25/85          $139.00
01/31/85          $300.00
                ---------
        total:    $671.00

-------------------------

Account # ->      222
Transaction Date   Amount
----------------   ------
01/20/85           $35.00
02/02/85          $890.00
02/05/85          $287.00
04/09/85          $348.00
04/10/85          $761.00
04/21/85          $501.00
04/22/85           $10.00
                ---------
        total:  $2,832.00

-------------------------

Account # ->      437
Transaction Date   Amount
----------------   ------
01/01/85
01/10/85          $100.00
01/27/85          $200.00
                ---------
        total:    $300.00

=========================
A. Total Accts:         3
B. Total:       $3,803.00
C. Average B/A: $1,267.67
=========================



******************
DAY OF WEEK FINDER
******************
Product: R:base 5000
version 1.0, 1.01

Use this "function" macro WEEKDAY.CMD to find the day of the week for any
date.

To run WEEKDAY.CMD, issue the following command line (this example uses
12/25/85, but you can pass any date in):

RUN WEEKDAY.CMD USING 12/25/85

When finished processing, the day of the week for 12/25/85 will be stored
in the global variable DAYFIND.

The code for WEEKDAY.CMD is listed below.  This macro makes use of modular
math and provides a good example of stacking IF blocks and using the
asterisk wildcard.

*( WEEKDAY.CMD)
SET VAR frstdate TO 01/01/01
LABEL top
SET VAR diff REAL
NEWPAGE
SET VAR finddate TO .%1
SET VAR diff TO +
.finddate - .frstdate

IF diff LT 0 THEN
WRITE "Sorry, has to be +
later than " AT 9 10
SHOW VAR frstdate AT 9 38
GOTO TOP
ENDIF

SET VAR diff TO .diff / 7
SET VAR diff TEXT

IF diff EQ "*.0*" THEN
SET VAR dayfind TO "Tuesday"
ENDIF

IF diff EQ "*.1*" THEN
SET VAR dayfind TO "Wednesday"
ENDIF

IF diff EQ "*.2*" THEN
SET VAR dayfind TO "Thursday"
ENDIF

IF diff EQ "*.4*" THEN
SET VAR dayfind TO "Friday"
ENDIF

IF diff EQ "*.5*" THEN
SET VAR dayfind TO "Saturday"
ENDIF

IF diff EQ "*.7*" THEN
SET VAR dayfind TO "Sunday"
ENDIF

IF diff EQ "*.8*" THEN
SET VAR dayfind TO "Monday"
ENDIF

WRITE "Day-of-week was/is: " +
AT 10 10
SHOW VAR dayfind AT 10 38




*************
CREDITS, ETC.
*************


PUBLICATION INFORMATION
Microrim BBS Technical Editorials are published twice a month by Microrim,
Inc.  [phone].  All inquiries, responses to this BBS Technical
Editorial, and contributions should be addressed to Kay D. Dayss, BBS
Technical Editorials Editor, Microrim, Inc., 3380 - 146th Pl. SE, Bellevue,
WA  98007.

ACKNOWLEDGEMENTS
The following Microrim employees contributed programs, concepts, and/or
articles for this Technical Editorial:

Kelly Cline
Marva Dasef
Kay Dayss


COPYRIGHT
Copyright (c) 1986 by Microrim, Inc.  All rights reserved.  No part of this
publication may be reproduced, transmitted, transcribed, stored in a
retrieval system, or translated into any language in any form by any means,
without the written permission of Microrim, Inc.


TRADEMARK
R:base is a registered trademark of Microrim, Inc.
MICRORIM is a registered trademark of Microrim, Inc.
R:BASE SERIES is a trademark of Microrim, Inc.
Application EXPRESS is a trademark of Microrim, Inc.
FileGateway is a trademark of Microrim, Inc.
CLOUT is a registered trademark of Microrim, Inc.
XRW is a trademark of Microrim, Inc.
IBM is a registered trademark of International Business Machines Corp.
PC XT and AT are trademarks of International Business Machines Corp.
PC DOS is a trademark of Internation Business Machines Corp.


DISCLAIMER
Microrim, Inc., makes no representation or warranties with respect to the
contents hereof, and specifically disclaims any implied warranties of
merchantability or fitness for any particular purpose.  Further, Microrim,
Inc., reserves the right to revise this publication and to make changes in
the content hereof without obligation to notify any person of such revision
or change and shall not be liable for errors contained herein or for
incidental or consequential damages in connection with the furnishing,
performance, or use of this material.